﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using QUANLIHOCSINH.BussinessObject;
using QUANLIHOCSINH.DataAccess;
namespace QUANLIHOCSINH.BussinessLogic
{
    class BANGDIEMBUS
    {
        ConnectData dataConn = new ConnectData();
   

        public DataTable LayDanhSachPhanLop(string magv , string mahocky, string mamon)
        {
            string sql = " select h.*,l.magv from bangdiem  as h left join lophoc as l on h.malop = l.malop ";
            sql += " where l.magv = '"+magv+"' and h.mahocky = '"+ mahocky +"' and h.mamon = '"+mamon+"' ";
            return dataConn.GetData(sql);
        }
        public void SuaBangDiem(BANGDIEM bd)
        {
            string sql = string.Format("UPDATE BANGDIEM SET Diem_M1 = '{0}' , Diem_M2 = '{1}' , Diem_15_1 = '{2}' , Diem_15_2 = '{3}' , Diem_15_3 = '{4}', Diem_15_4 = '{5}', Diem_1T_1= '{6}', Diem_1T_2= '{7}', Diem_Thi= '{8}' where STT = '{9}'",bd.diem_m1,bd.diem_m2,bd.diem_1T_1,bd.diem_15_2,bd.diem_15_3,bd.diem_15_4,bd.diem_1T_1,bd.diem_1T_2,bd.diem_thi,bd.stt );
            dataConn.ExecuteQuery(sql);
        }
        public string NextID()
        {
            if (dataConn.GetLastID("bangdiem", "stt").Equals(""))
            {
                return "1";
            }
            else
            {
                return Utility.LayMaTuDong(dataConn.GetLastID("bangdiem", "stt"), "");
            }
        }
        public void chuyenbangdiem(string manamhoc, string malop, string mahocsinh)
        {
            string sql = string.Format("UPDATE BANGDIEM SET MaLop = '{0}'  where mahocsinh = '{1}' and manamhoc = '{2}'", malop, mahocsinh, manamhoc);
            dataConn.ExecuteQuery(sql);
        }
        public void xoabangdiem(string manamhoc, string malop, string mahocsinh)
        {
            string sql = string.Format("DELETE FROM  BANGDIEM  where malop = '{0}' and mahocsinh = '{1}' and manamhoc = '{2}'", malop, mahocsinh, manamhoc);
            dataConn.ExecuteQuery(sql);
        }
        public void KhoiTaoBangDiem(string manamhoc, string malop, string mahocsinh)
        {
            DataTable tbmonhoc = dataConn.GetData("select *  from  MonHoc");
            DataTable tbhocki = dataConn.GetData("select *  from  HocKy");
            int hocki = tbhocki.Rows.Count;
            int somon = tbmonhoc.Rows.Count;

            for (int i = 1; i <= hocki; i++)
            {

                for (int j = 0; j < somon; j++)
                {
                    DataRow rowUser = tbmonhoc.Rows[j];
                    if (this.NextID() != "")
                    {
                        string sql = "INSERT INTO BANGDIEM (stt,mamon,mahocky,manamhoc,mahocsinh,malop,diem_m1,diem_m2,diem_15_1,diem_15_2,diem_15_3,diem_15_4,diem_1t_1,diem_1t_2,diem_thi)  VALUES ('" + this.NextID() + "','" + rowUser["MaMon"].ToString() + "','" + i + "','" + manamhoc + "','" + mahocsinh + "','" + malop + "','0','0','0','0','0','0','0','0','0')";
                        dataConn.ExecuteQuery(sql);
                    }
                    else
                    {
                        string sql = "INSERT INTO BANGDIEM (stt,mamon,mahocky,manamhoc,mahocsinh,malop,diem_m1,diem_m2,diem_15_1,diem_15_2,diem_15_3,diem_15_4,diem_1t_1,diem_1t_2,diem_thi)  VALUES ('1','" + rowUser["MaMon"].ToString() + "','" + i + "','" + manamhoc + "','" + mahocsinh + "','" + malop + "','0','0','0','0','0','0','0','0','0')";
                        dataConn.ExecuteQuery(sql);
                    }
                }
            }
        }
    }
}
